//
//  DB_UnComfirmIAPOrderForm.m
//  KCiPhoneUnBreakPrison
//
//  Created by tanyu on 2/16/12.
//  Copyright (c) 2012 __MyCompanyName__. All rights reserved.
//

#import "DB_UnComfirmIAPOrderForm.h"
static DB_UnComfirmIAPOrderForm *g_instance = nil;
NSString *SynchronizedObject = @"SynchronizedObject";
NSString *DataFileName = @"DB_UnComfirmIAPOrderForm.sqlite3";
NSString *DBTable_Name = @"receip";

#define DispatchQueueLable   "DB_UnComfirmIAPOrderForm_BackgroundQueue"

@interface DB_UnComfirmIAPOrderForm(private)

//打开数据库
- (BOOL)OpenDataBase;

//关闭数据库
- (BOOL)CloseDataBase;

//创建数据库表
- (BOOL)CreateTables;

- (BOOL)EnsureReady;

- (BOOL)private_updateRecharge_resultWithRecordID:(NSInteger)recordIDArg
                                            Value:(NSInteger)valueArg;

@end

@implementation DB_UnComfirmIAPOrderForm
+ (DB_UnComfirmIAPOrderForm *)shareInstance
{
    if (nil == g_instance)
    {
        @synchronized(SynchronizedObject)
        {
            if (nil == g_instance)
            {
                g_instance = [[DB_UnComfirmIAPOrderForm alloc] init];
            }
        }
    }
    
    return g_instance;
}

- (id) init
{
    self = [super init];
    if (self)
    {
        _bOpen = NO;
        backgroundQueue = dispatch_queue_create(DispatchQueueLable, NULL);
    }
    
    return self;
}

- (void)dealloc
{
    [self CloseDataBase];
//    dispatch_release(backgroundQueue);
}

- (NSString*) databasePath
{
	NSArray *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
	NSString *pathname = [path objectAtIndex:0];
	return [pathname stringByAppendingPathComponent:DataFileName];
}

// 打开数据库
- (BOOL) OpenDataBase
{
    BOOL bRet = YES;
    
    if (sqlite3_open([[self databasePath] UTF8String], &_database) != SQLITE_OK)
    {
		sqlite3_close(_database);
        
        bRet = NO;
	}
    
    return bRet;
}
// 关闭数据库
- (BOOL) CloseDataBase
{
    if (_bOpen)
    {
        _bOpen = NO;
        sqlite3_close(_database);
    }
    return TRUE;
}

// 创建数据库
- (BOOL) CreateTables
{
    // 创建表
    NSString *createDBCallList = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@("
                                  "[record_id] INTEGER PRIMARY KEY ASC AUTOINCREMENT DEFAULT 1,"
                                  "[brand_id] VARCHAR(100),"
                                  "[product_id] VARCHAR(100),"
                                  "[quantity] INTEGER,"
                                  "[receip] blob,"
                                  "[transacton_id] VARCHAR(100),"
                                  "[recharge_result] INTEGER,"
                                  "[order_no] VARCHAR(100)"
                                  ")", DBTable_Name];
    
    int sql_ret = 0;
    
	if ( (sql_ret = sqlite3_exec(_database, [createDBCallList UTF8String], NULL, NULL, NULL)) != SQLITE_OK)
    {
		sqlite3_close(_database);
        NSLog(@"Create DB_Order fail: %d", sql_ret);
        
        // 关闭数据库
        [self CloseDataBase];
        
        return NO;
	}
    
    return YES;
}

- (BOOL)EnsureReady
{
    if (!_bOpen)
    {
        if ( ![self OpenDataBase] || ![self CreateTables] )
        {
            return NO;
        }
    }
    
    return YES;
}

// 返回数据库表中的唯一标识
- (NSInteger)insertReceip:(NSData *)receipArg
                  brandID:(NSString *)brandIDArg // 3GID，KC ID等
                productID:(NSString *)productIDArg
                 quantity:(NSInteger)quantityArg
              trasactonID:(NSString *)transactonIDArg
{
    __block NSInteger iRet = -1;
    dispatch_sync(backgroundQueue, ^(void)
                  {
                      if ([self EnsureReady])
                      {
                          sqlite3_stmt *insertStmt;
                          NSString *sqlStr =[NSString stringWithFormat:@"INSERT INTO %@(record_id,brand_id,product_id,quantity,receip,transacton_id,recharge_result)  VALUES(?,?,?,?,?,?,?)", DBTable_Name];
                          int sqlite_ret = 0;
                          if( (sqlite_ret = sqlite3_prepare_v2(_database, [sqlStr UTF8String], -1, &insertStmt, NULL)) != SQLITE_OK)
                          {
                              //handle error
                              NSAssert(sqlite_ret == 0, @"sqlite3_prepare_v2 error with code:%d", sqlite_ret);
                          }
                          sqlite3_bind_text(insertStmt, 2, [brandIDArg cStringUsingEncoding:NSASCIIStringEncoding], (int)[brandIDArg length], NULL);
                          sqlite3_bind_text(insertStmt, 3, [productIDArg cStringUsingEncoding:NSASCIIStringEncoding], (int)[productIDArg length], NULL);
                          sqlite3_bind_int(insertStmt, 4, (int)quantityArg);
                          sqlite3_bind_blob(insertStmt, 5, [receipArg bytes], (int)[receipArg length], NULL);
                          sqlite3_bind_text(insertStmt, 6, [transactonIDArg cStringUsingEncoding:NSASCIIStringEncoding], (int)[transactonIDArg length], NULL);
                          sqlite3_bind_int(insertStmt, 7, 0);
                          
                          if ( (sqlite_ret = sqlite3_step(insertStmt))  != SQLITE_DONE)
                          {
                              NSAssert(sqlite_ret == 0, @"sqlite3_prepare_v2 error with code:%d", sqlite_ret);
                          }
                          
                          if ( (sqlite_ret = sqlite3_finalize(insertStmt)) != SQLITE_OK)
                          {
                              NSAssert(sqlite_ret == 0, @"sqlite3_finalize error with code:%d", sqlite_ret);
                          }
                          
                          //查询主键
                          NSString *selectStr = [NSMutableString stringWithFormat:@"SELECT * FROM %@"
                                                 " WHERE brand_id = '%@'"
                                                 " AND quantity = %ld"
                                                 " AND transacton_id = '%@'"
                                                 " ORDER BY record_id DESC LIMIT 1",
                                                 DBTable_Name,
                                                 brandIDArg, (long)quantityArg, transactonIDArg];
                          sqlite3_stmt *statement;
                          if ( (sqlite_ret = sqlite3_prepare_v2(_database, [selectStr UTF8String], -1, &statement, nil)) == SQLITE_OK)
                          {
                              while ( (sqlite_ret = sqlite3_step(statement) == SQLITE_ROW) ) {
                                  iRet = sqlite3_column_int(statement, 0);
                              }
                              NSLog(@"sql ret = %d", sqlite_ret);
                          } else
                          {
                              NSLog(@"Eror:%d", sqlite_ret);
                          }
                          
                          if ( (sqlite_ret = sqlite3_finalize(statement)) != SQLITE_OK)
                          {
                              NSAssert(sqlite_ret == 0, @"sqlite3_finalize error with code:%d", sqlite_ret);
                          }
                          
                          [self CloseDataBase];
                      } // if ([self EnsureReady])
                  });
    
    return iRet;
}

- (BOOL)deleteReceipWithID:(NSInteger)receipIDArg
{
    __block BOOL rv = NO;
    dispatch_sync(backgroundQueue, ^(void)
                  {
                      if ([self EnsureReady])
                      {
                          int sqlite_ret = 0;
                          
                          NSString *sqlStr = [NSString stringWithFormat:@"DELETE FROM %@ WHERE record_id = %ld", DBTable_Name, (long)receipIDArg];
                          if( (sqlite_ret = sqlite3_exec (_database, [sqlStr UTF8String], NULL, NULL, NULL)) != SQLITE_OK)
                          {
                              NSAssert(sqlite_ret == 0, @"sqlite3_finalize error with code:%d", sqlite_ret);
                          }
                          
                          [self CloseDataBase];
                          rv = YES;
                      }
                  });
    
    return YES;
}

// 如果数据库中有则将返回值通过参数返回，返回值为YES.反之返回NO
// 如果参数为nil或NULL则相当于测试数据库中是否还有未发送至服务器的receip
//- (BOOL)getReceipt:(NSData **)receipDataArg
//          recordID:(NSInteger *)recordIDArg
//           brandID:(NSString **)brandIDArg // 3GID，KC ID等
//         productID:(NSString **)productIDArg
//          quantity:(NSInteger *)quantityArg
//       trasactonID:(NSString **)transactonIDArg
- (BOOL)getRecord:(NSMutableDictionary *)dict
{
    __block BOOL ret = NO;
    
    __block NSMutableData *receipDataArg = [NSMutableData data];
    __block NSMutableString *recordIDArg = [NSMutableString string];
    __block NSMutableString *brandIDArg = [NSMutableString string];
    __block NSMutableString *productIDArg = [NSMutableString string];
    __block NSMutableString *quantityArg = [NSMutableString string];
    __block NSMutableString *transactonIDArg = [NSMutableString string];
    __block NSMutableString *orderNoArg = [NSMutableString string]; //交易订单号
    
    dispatch_sync(backgroundQueue, ^(void)
                  {
                      if ([self EnsureReady])
                      {
                          int sqlite_ret = 0;
                          
                          NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM %@ WHERE recharge_result = 0 ORDER BY record_id ASC", DBTable_Name];
                          //      NSString *sqlStr = [NSString stringWithFormat:@"SELECT * FROM %@ ORDER BY record_id ASC", DBTable_Name];
                          sqlite3_stmt *stmt;
                          if ( (sqlite_ret = sqlite3_prepare_v2(_database, [sqlStr UTF8String], -1, &stmt, NULL)) != SQLITE_OK)
                          {
                              NSAssert(sqlite_ret == 0, @"sqlite3_prepare_v2 error with code:%d", sqlite_ret);
                          }
                          
                          if ((sqlite_ret = sqlite3_step(stmt)) == SQLITE_ROW)
                          {
                              const void *op = sqlite3_column_blob(stmt, 4);
                              int size = sqlite3_column_bytes(stmt,4);
                              [receipDataArg appendBytes:op length:size];
                              [recordIDArg setString:[NSString stringWithFormat:@"%d", sqlite3_column_int(stmt, 0)]];
                              [brandIDArg setString:[NSString stringWithFormat:@"%s", sqlite3_column_text16(stmt, 1)]];
                              [productIDArg setString:[NSString stringWithFormat:@"%s", sqlite3_column_text16(stmt, 2)]];
                              [quantityArg setString:[NSString stringWithFormat:@"%d", sqlite3_column_int(stmt, 3)]];
                              [transactonIDArg setString:[NSString stringWithFormat:@"%s", sqlite3_column_text16(stmt, 5)]];
                              [orderNoArg setString:[NSString stringWithFormat:@"%s", sqlite3_column_text16(stmt, 7)]];
                              
                              if ( (sqlite_ret = sqlite3_finalize(stmt)) != SQLITE_OK)
                              {
                                  NSAssert(sqlite_ret == 0, @"sqlite3_finalize error with code:%d", sqlite_ret);
                              }
                              
                              ret = [self private_updateRecharge_resultWithRecordID:[recordIDArg intValue] Value:1];
                              
                              ret = [self CloseDataBase];
                              
                          } else if (sqlite_ret != SQLITE_DONE)
                          {
                              NSAssert(sqlite_ret == SQLITE_DONE, @"sqlite_step error with code:%d", sqlite_ret);
                          }
                          
                      } //  if (![self EnsureReady])                  
                  });
    
    [dict setObject:receipDataArg forKey:@"receip"];
    [dict setObject:recordIDArg forKey:@"record_id"];
    [dict setObject:brandIDArg forKey:@"brand_id"];
    [dict setObject:productIDArg forKey:@"productID"];
    [dict setObject:quantityArg forKey:@"quantity"];
    [dict setObject:transactonIDArg forKey:@"transactionID"];
    [dict setObject:orderNoArg forKey:@"order_no"];
    NSLog(@"%@", productIDArg);
    return ret;
}

- (BOOL)private_updateRecharge_resultWithRecordID:(NSInteger)recordIDArg
                                            Value:(NSInteger)valueArg
{
    if (![self EnsureReady])
    {
        return NO;
    }
    
    int sqlite_ret = 0;
    NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET recharge_result = %ld WHERE record_id = %ld", DBTable_Name, (long)valueArg, (long)recordIDArg];
    if( (sqlite_ret = sqlite3_exec (_database, [sqlStr UTF8String], NULL, NULL, NULL)) != SQLITE_OK)
    {
        NSAssert(sqlite_ret == 0, @"sqlite3_finalize error with code:%d", sqlite_ret);
    }
    
    [self CloseDataBase];
    
    return YES;
    
}
- (BOOL)updateRecharge_resultWithRecordID:(NSInteger)recordIDArg
                                    Value:(NSInteger)valueArg
{
    __block BOOL ret = NO;
    dispatch_sync(backgroundQueue, ^(void)
                  {
                      ret = [self private_updateRecharge_resultWithRecordID:recordIDArg Value:valueArg];
                  });
    return ret;
}

/**
 *  <#Description#>
 *
 *  @param orderNo  <#orderNo description#>
 *  @param recordID <#recordID description#>
 *
 *  @return <#return value description#>
 */
- (BOOL)private_updateRecharge_resultWithOrderNo:(NSString*)orderNo
                                        recordID:(NSInteger)recordID{
    if (![self EnsureReady])
    {
        return NO;
    }
    
    int sqlite_ret = 0;
    NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET order_no ='%@' WHERE record_id = %ld", DBTable_Name, orderNo, (long)recordID];
    if( (sqlite_ret = sqlite3_exec (_database, [sqlStr UTF8String], NULL, NULL, NULL)) != SQLITE_OK)
    {
        NSAssert(sqlite_ret == 0, @"sqlite3_finalize error with code:%d", sqlite_ret);
    }
    
    [self CloseDataBase];
    
    return YES;
}

- (BOOL)private_updateRecharge_resultWithOrderNo:(NSString*)orderNo value:(NSInteger)valueArg
                                        recordID:(NSInteger)recordID{
    if (![self EnsureReady])
    {
        return NO;
    }
    
    int sqlite_ret = 0;
    NSString *sqlStr = [NSString stringWithFormat:@"UPDATE %@ SET order_no ='%@',recharge_result = %ld WHERE record_id = %ld", DBTable_Name, orderNo,(long)valueArg,(long)recordID];
    if( (sqlite_ret = sqlite3_exec (_database, [sqlStr UTF8String], NULL, NULL, NULL)) != SQLITE_OK)
    {
        NSAssert(sqlite_ret == 0, @"sqlite3_finalize error with code:%d", sqlite_ret);
    }
    
    [self CloseDataBase];
    
    return YES;
}

/**
 *  更新值订单号值
 *
 *  @param orderNo  订单id
 *  @param recordID 记录id
 *
 *  @return
 */
- (BOOL)updateRecharge_resultWithOrderNo:(NSString*)orderNo
                                recordID:(NSInteger)recordID{
    __block BOOL ret = NO;
    dispatch_sync(backgroundQueue, ^(void)
                  {
                      ret = [self private_updateRecharge_resultWithOrderNo:orderNo recordID:recordID];
                      
                  });
    return ret;
}
/**
 *  更新值订单号值与未上传服务器状态
 *
 *  @param orderNo  订单id
 *  @param valueArg 上传状态
 *  @param recordID 记录id
 *
 *  @return
 */
- (BOOL)updateRecharge_resultWithOrderNo:(NSString*)orderNo value:(NSInteger)valueArg recordID:(NSInteger)recordID{
    __block BOOL ret = NO;
    dispatch_sync(backgroundQueue, ^(void)
                  {
                      ret = [self private_updateRecharge_resultWithOrderNo:orderNo value:valueArg recordID:recordID];
                      
                  });
    return ret;
}
@end
